﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Adex;
using Telerik.Web.UI;
using SubSonic.Sugar;
using System.Security;
using MediaMonitor.Common;
using Ionic.Zip;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Drawing;

namespace MediaMonitor.Users
{
    public partial class Report_Outdoor : Adex.BasePage
    {
        //DateTime sDate = Convert.ToDateTime("01/01/2000"), eDate = Convert.ToDateTime("01/01/2000");
        int mediaTypeCode = 0;
        string mediaCodes = "";
        int programCode = 0;
        int sectorCode = 0, subSectorCode = 0, subSector2Code = 0, categoryCode = 0;
        int advertiserCode = 0, brandCode = 0, productCode = 0, versionCode = 0, agencyCode = 0;
        int sTime = 0, eTime = 0;
        int size = 0;
        string quality = "";
        int typeofads = 0;
        int sPrice = 0, ePrice = 0;
        bool firstAppearanceOnly = false;
        bool downloadOnly = false;
        string cols = "";
        bool downloadOnlyProduct = false;
        bool downloadOnlyAdv = false;

        protected void Page_Init(object sender, EventArgs e)
        {

        }

        protected void btnDownloadAllSummary_Clicked(object sender, EventArgs e)
        {
            CommonDownloadAll();
        }

        protected void btnDownloadAll_Clicked(object sender, EventArgs e)
        {
            CommonDownloadAll();

        }


        private void CommonDownloadAll()
        {
            Int32 MediaType = 0;
            Int32 CategoryID = 0;
            String CategoryName = String.Empty;
            MediaType = Utility.ToInt(Utility.GetRequestedVal("mtc", "0"));
            CategoryID = Utility.ToInt(Utility.GetRequestedVal("cc", "0"));
            DataTable dt = RfController.GetVideosAudiosCategory(MediaType, CategoryID);

            if (dt.Rows.Count > 0)
            {
                #region[Zip Format]
                Response.Clear();
                // no buffering - allows large zip files to download as they are zipped
                Response.BufferOutput = false;
                String filePath = String.Empty;
                String filePath1 = String.Empty;
                String downloadFileName = dt.Rows[0]["CategoryName"].ToString().Replace("/", "") + ".zip";
                downloadFileName = downloadFileName.Replace(" ", "_");

                Response.ContentType = "application/zip";
                Response.AddHeader("content-disposition", "filename=" + downloadFileName);

                using (ZipFile zip = new ZipFile())
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        String filePathCheck = String.Empty;
                        String filePathCheck1 = String.Empty;

                        String CatName = String.Empty;
                        filePath = row["NewOutDoorFile"].ToString();
                        filePath1 = row["StreetGraph"].ToString();

                        CatName = row["CategoryName"].ToString().Replace("/", "");
                        CatName = CatName.Replace(" ", "_"); ;

                        filePathCheck = Server.MapPath("../outdoor/" + filePath);
                        filePathCheck1 = Server.MapPath("../outdoor-street/" + filePath1);

                        if (filePath != "")
                        {
                            if (File.Exists(filePathCheck))
                            {
                                try
                                {
                                    zip.AddFile(filePathCheck, CatName);
                                }
                                catch { }
                            }
                        }

                        if (filePath1 != "")
                        {
                            if (File.Exists(filePathCheck1))
                            {
                                try
                                {
                                    zip.AddFile(filePathCheck1, CatName);
                                }
                                catch { }
                            }
                        }

                    }
                    zip.Save(Response.OutputStream);
                }
                Response.Close();
                Response.End();


                #endregion
            }
            else
            {
                AjaxFuncs.MsgBox(this.Page, "No record exists!");
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            downloadOnly = Utility.ToBool(Utility.GetRequestedVal("down", "0"));
            downloadOnlyProduct = Utility.ToBool(Utility.GetRequestedVal("downpro", "0"));
            downloadOnlyAdv = Utility.ToBool(Utility.GetRequestedVal("downadv", "0"));

            if (downloadOnly)
            {
                pnlCategory.Visible = true;
                pnlSummaryCategory.Visible = true;
            }
            else
            {
                pnlCategory.Visible = false;
                pnlSummaryCategory.Visible = false;
            }

            if (downloadOnlyProduct)
            {
                PnlProduct.Visible = true;
                pnlSummaryProduct.Visible = true;
            }
            else
            {
                PnlProduct.Visible = false;
                pnlSummaryProduct.Visible = false;
            }
            if (downloadOnlyAdv)
            {
                PnlAdv.Visible = true;
                pnlSummaryAdv.Visible = true;
            }
            else
            {
                PnlAdv.Visible = false;
                pnlSummaryAdv.Visible = false;
            }



            if (!Page.IsPostBack)
            {
                ViewState["dvSummaryOutDoor"] = null;
            }
        }

        private void PrepareGridColumns(string cols)
        {



            radGrid.Columns.FindByUniqueName("RFDate").Visible = cols.Contains("RFDate");
            radGrid.Columns.FindByUniqueName("MediaType").Visible = cols.Contains("MediaTypeName");
            radGrid.Columns.FindByUniqueName("Media").Visible = cols.Contains("MediaName");

            radGrid.Columns.FindByUniqueName("Sector").Visible = cols.Contains(" SectorName"); //a space before sectorname to keep a difference from SubSectorName 
            radGrid.Columns.FindByUniqueName("SubSector").Visible = cols.Contains("SubSectorName");
            radGrid.Columns.FindByUniqueName("Category").Visible = cols.Contains("CategoryName");

            radGrid.Columns.FindByUniqueName("Media").Visible = cols.Contains("MediaName");
            radGrid.Columns.FindByUniqueName("Agency").Visible = cols.Contains("AgencyName");
            radGrid.Columns.FindByUniqueName("Advertiser").Visible = cols.Contains("AdvertiserName");
            radGrid.Columns.FindByUniqueName("Brand").Visible = cols.Contains("BrandName");
            radGrid.Columns.FindByUniqueName("Product").Visible = cols.Contains("ProductName");
            radGrid.Columns.FindByUniqueName("ProdVersion").Visible = cols.Contains("ProdVersionName");
            radGrid.Columns.FindByUniqueName("STime").Visible = cols.Contains("TimeStartFormatted");
            radGrid.Columns.FindByUniqueName("ETime").Visible = cols.Contains("TimeEndFormatted");

            radGrid.Columns.FindByUniqueName("Size").Visible = cols.Contains("Size");
            radGrid.Columns.FindByUniqueName("Quality").Visible = cols.Contains("Quality");
            radGrid.Columns.FindByUniqueName("TypeOfAds").Visible = cols.Contains("TypeOfAds");
            radGrid.Columns.FindByUniqueName("VisibilityCode").Visible = cols.Contains("VisibilityCode");
            radGrid.Columns.FindByUniqueName("CarrierCode").Visible = cols.Contains("CarrierCode");
            radGrid.Columns.FindByUniqueName("BetweenAvenue").Visible = cols.Contains("BetweenAvenue");

            radGrid.Columns.FindByUniqueName("RealCost").Visible = cols.Contains("RealCost");

            lnkGrp_Adv.Visible = cols.Contains("AdvertiserName");
            lnkGrp_Agnc.Visible = cols.Contains("AgencyName");
            lnkGrp_Brnd.Visible = cols.Contains("BrandName");
            lnkGrp_Prod.Visible = cols.Contains("ProductName");
            lnkGrp_Media.Visible = cols.Contains("MediaName");
            //lnkGrp_Prog.Visible = cols.Contains("ProgramName");

            GridBoundColumn InvestmentColumn = radGrid.Columns.FindByUniqueName("RealCost") as GridBoundColumn;

            switch (AppUser.GetCountry())
            {
                case 58:
                    {
                        InvestmentColumn.DataFormatString = "<small>RD$ <b>{0:N2}</b><small>";
                        break;
                    }
                case 218:
                    {
                        InvestmentColumn.DataFormatString = "<small>TT$ <b>{0:N2}</b><small>";
                        break;
                    }
            }
        }

        protected void radGrid_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {
          

        }




        protected void ObjDS_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
        {
            if (!e.ExecutingSelectCount)
            {
                e.Arguments.MaximumRows = radGrid.PageSize;
                e.InputParameters.Add("e", e);
            }
        }



        protected void radGrid_PreRender(object sender, EventArgs e)
        {
           
        }

        protected void radGrid_ItemCommand(object source, GridCommandEventArgs e)
        {
            if (e.CommandName == "Add2Cart")
            {

                int pvID = Utility.ToInt(e.Item.OwnerTableView.DataKeyValues[e.Item.ItemIndex]["ProdVersionID"].ToString());
                int pdID = Utility.ToInt(e.Item.OwnerTableView.DataKeyValues[e.Item.ItemIndex]["ProductID"].ToString());
                decimal price = Utility.ToDecimal(e.Item.OwnerTableView.DataKeyValues[e.Item.ItemIndex]["Price"]);
                string outdoorFile = e.Item.OwnerTableView.DataKeyValues[e.Item.ItemIndex]["NewOutDoorFile"].ToString();

                int mtCode = Utility.ToInt(e.Item.OwnerTableView.DataKeyValues[e.Item.ItemIndex]["MediaTypeCode"].ToString());

                Boolean checkVideoAudioImage = false;

                if (mtCode == 7) //OUTDOOR
                {
                    checkVideoAudioImage = Utility.CheckOutDoorAvailability(Utility.ToStr(outdoorFile));
                }


                if (checkVideoAudioImage == false)
                {
                    AjaxFuncs.MsgBox(this.Page,
                                     "The video, audio or image is temporarily unavailable and not create an order with an empty video, audio or image.");
                    return;
                }


                hdnPV.Value = pvID.ToString();
                hdnPD.Value = pdID.ToString();
                hdnPrice.Value = price.ToString();
                hdnOutDoorFile.Value = outdoorFile;



                int existingOrderWithSameItem = Cart.FindOrderByProdVersion(pvID, AppUser.CurrUserID);

                if (existingOrderWithSameItem > 0)
                {
                    string warningMsg = "The video you want to purchase is already present in your Order #<b>" + existingOrderWithSameItem.ToString() + "</b>.<br><br> Do you still want to purchas this Outdoor file?";
                    ltrWarningOrderNum.Text = warningMsg;
                    pnl.Visible = true;
                }
                else
                {
                    Add2Cart(pvID, pdID, price, outdoorFile);
                    //ShowPop(this.Page, false);

                }


            }//end Add2Cart
        }

        private void Add2Cart(int pvID, int pdID, decimal price, string outdoorFile)
        {


            VProdVersion pv = new VProdVersion("ProdVersionID", pvID);

            string pvName = pv.ProdVersionName;
            string pdName = pv.ProductName;
            string svf = Utility.ToStr(pv.ShortVideoFile);

            string fvf = Utility.ToStr(pv.FullVideoFile);

            bool sv = Utility.ToBool(pv.ShortVideo);

            bool fv = Utility.ToBool(pv.FullVideo);

            string prFile = "";
            string odFile = outdoorFile;

            string rdFile = "";
            string cbFile = "";
            string inFile = "";

            int mtCode = Utility.ToInt(MediaType.Query().SetSelectList("MediaTypeCode").WHERE("MediaTypeName", "OutDoor").ExecuteScalar());

            if (Cart.AddItem(Cart.CreateCartItem(mtCode, pv.ProdVersionID, pvName, pv.ProductID, pdName, price, svf, fvf, sv, fv, prFile, odFile, rdFile, cbFile, inFile)) == true)
            {
                AjaxFuncs.MsgBox(this.Page, "Selected file added to your cart successfully");
            }
            else
            {
                AjaxFuncs.MsgBox(this.Page, "Already added to your cart");
            }

        }


        protected void lnkGrp_Clicked(object sender, EventArgs e)
        {


            LinkButton lnk = ((LinkButton)(sender));
            string grp = lnk.CommandArgument;

            if (lnk.Text.StartsWith("Group"))
            {
                //radGrid.MasterTableView.GroupByExpressions.Add(new GridGroupByExpression(grp + " Group By " + grp));
                radGrid.MasterTableView.GroupLoadMode = GridGroupLoadMode.Server;
                radGrid.MasterTableView.GroupsDefaultExpanded = true;

                radGrid.MasterTableView.GroupByExpressions.Add(grp + " Group By " + grp);
                lnk.ForeColor = System.Drawing.Color.Red;
                lnk.Font.Bold = true;
                lnkClearGroup.Visible = true;
            }

            radGrid.Rebind();

        }

        protected void lnkClearGroup_Clicked(object sender, System.EventArgs e)
        {
            radGrid.MasterTableView.GroupByExpressions.Clear();
            radGrid.Rebind();

            lnkGrp_Adv.ForeColor = lnkGrp_Agnc.ForeColor = lnkGrp_Brnd.ForeColor = lnkGrp_Media.ForeColor = lnkGrp_Prod.ForeColor = System.Drawing.Color.PowderBlue;

            lnkGrp_Adv.Font.Bold = lnkGrp_Agnc.Font.Bold = lnkGrp_Brnd.Font.Bold = lnkGrp_Media.Font.Bold = lnkGrp_Prod.Font.Bold = true;

            lnkClearGroup.Visible = false;
        }


        protected void btnExport_Clicked(object sender, EventArgs e)
        {

            String GetCols = String.Empty;

            InngridExport();

            GetCols = cols;// from InngridExport()


            objDSExport.SelectParameters["MediaTypeCode"].DefaultValue = Session["mtc"].ToString();
            DataView dv = new DataView();
            dv = (DataView)objDSExport.Select();

            dv = RemoveDataViewColumns(dv, GetCols);
            dv.Table.Columns.Remove("TotalRecords");

            DataTable dt = dv.ToTable();
            DumpExcel(dt);
        }

        private void DumpExcel(DataTable tbl)
        {
            Int32 tblColumnCount = tbl.Columns.Count;
            String strAutofilter = String.Empty;
            strAutofilter = ReturnCharacterByPassingInterger(tblColumnCount);

            if (tblColumnCount > 0)
            {
                using (ExcelPackage pck = new ExcelPackage())
                {
                    try
                    {
                        //Create the worksheet
                        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("MediaMonitor-OutdoorReport");

                        DataTable UserSelection = CreateDatatableofUserSelection();
                        ws.Cells["A1"].LoadFromDataTable(UserSelection, true);

                        ws.Cells["A10"].LoadFromDataTable(tbl, true);
                        ws.DefaultColWidth = 20;

                        ws.Cells["A10:" + strAutofilter + "10"].AutoFilter = true;

                        using (ExcelRange rng = ws.Cells["A10:" + strAutofilter + "10"])
                        {
                            rng.Style.Font.Bold = true;

                        }
                        using (ExcelRange rngUserSelection = ws.Cells["A1:B1"])
                        {
                            rngUserSelection.Style.Font.Bold = true;

                        }
                    }
                    catch { }
                    Response.Clear();
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;  filename=MediaMonitor-OutdoorReport.xlsx");
                    Response.BinaryWrite(pck.GetAsByteArray());
                    Response.End();
                }
            }
        }


        protected void btnCancelPurchase_Clicked(object sender, EventArgs e)
        {
            pnl.Visible = false;
        }

        protected void btnContinuePurchase_Clicked(object sender, EventArgs e)
        {
            pnl.Visible = false;
            Add2Cart(Utility.ToInt(hdnPV.Value), Utility.ToInt(hdnPD.Value), Utility.ToDecimal(hdnPrice.Value), hdnOutDoorFile.Value);


        }

        public static void ShowPop(Page aspxPage, bool show)
        {
            try
            {

                AjaxControlToolkit.ModalPopupExtender screen = (AjaxControlToolkit.ModalPopupExtender)aspxPage.FindControl("MPE");


                if ((screen == null))
                    return;

                if (show == true)
                {
                    screen.Show();

                }
                else
                {
                    screen.Hide();
                }
            }
            catch
            {
            }
        }


       

        protected void lnlBack_Clicked(object sender, System.EventArgs e)
        {
            InnerDIV.Style.Add("display", "none");
            InnerDIVTOP.Visible = false;
            radGridAll.Visible = true;
            SummaryDIVTOP.Visible = true;

        }

        protected void radGridAll_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {

            string sDate = Utility.GetRequestedVal("sd", "01_01_2000").Replace("_", "/");
            string eDate = Utility.GetRequestedVal("ed", "01_01_2000").Replace("_", "/");

            mediaTypeCode = Utility.ToInt(Utility.GetRequestedVal("mtc", "0"));

            bool searchFromAllMedia = Utility.ToBool(Utility.GetRequestedVal("mc", "false"));
            mediaCodes = searchFromAllMedia == false ? Session["SearchMediaCodes_outdoor"].ToString() : "";

            programCode = Utility.ToInt(Utility.GetRequestedVal("prg", "0"));

            sectorCode = Utility.ToInt(Utility.GetRequestedVal("s", "0"));
            subSectorCode = Utility.ToInt(Utility.GetRequestedVal("ss", "0"));
            categoryCode = Utility.ToInt(Utility.GetRequestedVal("cc", "0"));

            advertiserCode = Utility.ToInt(Utility.GetRequestedVal("adv", "0"));
            brandCode = Utility.ToInt(Utility.GetRequestedVal("brnd", "0"));
            productCode = Utility.ToInt(Utility.GetRequestedVal("prod", "0"));
            versionCode = Utility.ToInt(Utility.GetRequestedVal("ver", "0"));
            agencyCode = Utility.ToInt(Utility.GetRequestedVal("agn", "0"));

            sTime = Utility.ToInt(Utility.GetRequestedVal("st", "0"));
            eTime = Utility.ToInt(Utility.GetRequestedVal("et", "0"));



            size = Utility.ToInt(Utility.GetRequestedVal("size", "0"));
            quality = Utility.GetRequestedVal("quality", "");

            typeofads = Utility.ToInt(Utility.GetRequestedVal("typeofads", "0"));


            sPrice = Utility.ToInt(Utility.GetRequestedVal("sp", "0"));
            ePrice = Utility.ToInt(Utility.GetRequestedVal("ep", "0"));

            firstAppearanceOnly = Utility.ToBool(Utility.GetRequestedVal("fa", "0"));

            cols = Utility.GetRequestedVal("cols", "ProdVersionCode, ProductCode, ProdVersionName, ProductName, Price");

            cols = "MediaTypeCode, ProdVersionCode, ProductCode, OutdoorFile, NewOutdoorFile, " + cols;

            if (sDate != "1/1/2000" || eDate != "1/1/2000")
                cols = "RFDate, " + cols;

            if (!firstAppearanceOnly)
            {
                // cols += ", ShortVideo, FullVideo, ShortVideoFile, FullVideoFile, Duration, Price";
                cols += ", Price";
            }
            else
            {
                cols = cols.Replace("RFDate,", "");
                //cols += ", RFDate, ShortVideo, FullVideo, ShortVideoFile, FullVideoFile, Duration, Price";
                cols += ", RFDate, Price";
            }

            cols += ", NewOutDoorFile";

            objDSAll.SelectParameters["userID"].DefaultValue = AppUser.CurrUserID.ToString();
            objDSAll.SelectParameters["sDate"].DefaultValue = sDate.ToString();
            objDSAll.SelectParameters["eDate"].DefaultValue = eDate.ToString();
            objDSAll.SelectParameters["mediaTypeCode"].DefaultValue = mediaTypeCode.ToString();
            objDSAll.SelectParameters["mediaCodes"].DefaultValue = mediaCodes;
            objDSAll.SelectParameters["sectorCode"].DefaultValue = sectorCode.ToString();
            objDSAll.SelectParameters["subSectorCode"].DefaultValue = subSectorCode.ToString();
            objDSAll.SelectParameters["categoryCode"].DefaultValue = categoryCode.ToString();
            objDSAll.SelectParameters["advertiserCode"].DefaultValue = advertiserCode.ToString();
            objDSAll.SelectParameters["brandCode"].DefaultValue = brandCode.ToString();
            objDSAll.SelectParameters["productCode"].DefaultValue = productCode.ToString();
            objDSAll.SelectParameters["prodVersionCode"].DefaultValue = versionCode.ToString();
            objDSAll.SelectParameters["agencyCode"].DefaultValue = agencyCode.ToString();
            objDSAll.SelectParameters["sPrice"].DefaultValue = sPrice.ToString();
            objDSAll.SelectParameters["ePrice"].DefaultValue = ePrice.ToString();
            objDSAll.SelectParameters["size"].DefaultValue = size.ToString();
            objDSAll.SelectParameters["quality"].DefaultValue = quality.ToString();
            objDSAll.SelectParameters["typeOfAds"].DefaultValue = typeofads.ToString();
            objDSAll.SelectParameters["fa"].DefaultValue = firstAppearanceOnly.ToString();

            radGridAll.DataSourceID = "objDSAll";

            SummaryDIVTOP.Visible = true;

            DataView dvSummary = new DataView();
            dvSummary = (DataView)objDSAll.Select();
            ViewState["dvSummaryOutDoor"] = dvSummary;

        }
        private void Inngrid()
        {

            string sDate = Utility.GetRequestedVal("sd", "01_01_2000").Replace("_", "/");
            string eDate = Utility.GetRequestedVal("ed", "01_01_2000").Replace("_", "/");

            mediaTypeCode = Utility.ToInt(Utility.GetRequestedVal("mtc", "0"));

            bool searchFromAllMedia = Utility.ToBool(Utility.GetRequestedVal("mc", "false"));
            mediaCodes = searchFromAllMedia == false ? Session["SearchMediaCodes_outdoor"].ToString() : "";

            programCode = Utility.ToInt(Utility.GetRequestedVal("prg", "0"));

            sectorCode = Utility.ToInt(Utility.GetRequestedVal("s", "0"));
            subSectorCode = Utility.ToInt(Utility.GetRequestedVal("ss", "0"));
            categoryCode = Utility.ToInt(Utility.GetRequestedVal("cc", "0"));

            advertiserCode = Utility.ToInt(Utility.GetRequestedVal("adv", "0"));
            brandCode = Utility.ToInt(Utility.GetRequestedVal("brnd", "0"));
            productCode = Utility.ToInt(Utility.GetRequestedVal("prod", "0"));
            versionCode = Utility.ToInt(Utility.GetRequestedVal("ver", "0"));
            agencyCode = Utility.ToInt(Utility.GetRequestedVal("agn", "0"));

            sTime = Utility.ToInt(Utility.GetRequestedVal("st", "0"));
            eTime = Utility.ToInt(Utility.GetRequestedVal("et", "0"));



            size = Utility.ToInt(Utility.GetRequestedVal("size", "0"));
            quality = Utility.GetRequestedVal("quality", "");

            typeofads = Utility.ToInt(Utility.GetRequestedVal("typeofads", "0"));


            sPrice = Utility.ToInt(Utility.GetRequestedVal("sp", "0"));
            ePrice = Utility.ToInt(Utility.GetRequestedVal("ep", "0"));

            firstAppearanceOnly = Utility.ToBool(Utility.GetRequestedVal("fa", "0"));

            cols = Utility.GetRequestedVal("cols", "ProdVersionCode, ProductCode, ProdVersionName, ProductName, Price");

            cols = "MediaTypeCode, ProdVersionCode, ProductCode, OutdoorFile, NewOutdoorFile, " + cols;

            if (sDate != "1/1/2000" || eDate != "1/1/2000")
                cols = "RFDate, " + cols;

            if (!firstAppearanceOnly)
            {
                // cols += ", ShortVideo, FullVideo, ShortVideoFile, FullVideoFile, Duration, Price";
                cols += ", Price";
            }
            else
            {
                cols = cols.Replace("RFDate,", "");
                //cols += ", RFDate, ShortVideo, FullVideo, ShortVideoFile, FullVideoFile, Duration, Price";
                cols += ", RFDate, Price";
            }

            cols += ", NewOutDoorFile";

            objDS.SelectParameters["userID"].DefaultValue = AppUser.CurrUserID.ToString();
            objDS.SelectParameters["sDate"].DefaultValue = sDate.ToString();
            objDS.SelectParameters["eDate"].DefaultValue = eDate.ToString();
            objDS.SelectParameters["mediaTypeCode"].DefaultValue = mediaTypeCode.ToString();
            objDS.SelectParameters["mediaCodes"].DefaultValue = mediaCodes;
            objDS.SelectParameters["sectorCode"].DefaultValue = sectorCode.ToString();
            objDS.SelectParameters["subSectorCode"].DefaultValue = subSectorCode.ToString();
            objDS.SelectParameters["categoryCode"].DefaultValue = categoryCode.ToString();
            objDS.SelectParameters["advertiserCode"].DefaultValue = advertiserCode.ToString();
            objDS.SelectParameters["brandCode"].DefaultValue = brandCode.ToString();
            objDS.SelectParameters["productCode"].DefaultValue = productCode.ToString();
            objDS.SelectParameters["prodVersionCode"].DefaultValue = versionCode.ToString();
            objDS.SelectParameters["agencyCode"].DefaultValue = agencyCode.ToString();
            objDS.SelectParameters["sPrice"].DefaultValue = sPrice.ToString();
            objDS.SelectParameters["ePrice"].DefaultValue = ePrice.ToString();
            objDS.SelectParameters["size"].DefaultValue = size.ToString();
            objDS.SelectParameters["quality"].DefaultValue = quality.ToString();
            objDS.SelectParameters["typeOfAds"].DefaultValue = typeofads.ToString();
            objDS.SelectParameters["fa"].DefaultValue = firstAppearanceOnly.ToString();

            radGrid.DataSourceID = "objDS";
            PrepareGridColumns(cols);
        }

        private void InngridExport()
        {
            string sDate = Utility.GetRequestedVal("sd", "01_01_2000").Replace("_", "/");
            string eDate = Utility.GetRequestedVal("ed", "01_01_2000").Replace("_", "/");

            mediaTypeCode = Utility.ToInt(Utility.GetRequestedVal("mtc", "0"));

            bool searchFromAllMedia = Utility.ToBool(Utility.GetRequestedVal("mc", "false"));
            mediaCodes = searchFromAllMedia == false ? Session["SearchMediaCodes_outdoor"].ToString() : "";

            programCode = Utility.ToInt(Utility.GetRequestedVal("prg", "0"));

            sectorCode = Utility.ToInt(Utility.GetRequestedVal("s", "0"));
            subSectorCode = Utility.ToInt(Utility.GetRequestedVal("ss", "0"));
            categoryCode = Utility.ToInt(Utility.GetRequestedVal("cc", "0"));

            advertiserCode = Utility.ToInt(Utility.GetRequestedVal("adv", "0"));
            brandCode = Utility.ToInt(Utility.GetRequestedVal("brnd", "0"));
            productCode = Utility.ToInt(Utility.GetRequestedVal("prod", "0"));
            versionCode = Utility.ToInt(Utility.GetRequestedVal("ver", "0"));
            agencyCode = Utility.ToInt(Utility.GetRequestedVal("agn", "0"));

            sTime = Utility.ToInt(Utility.GetRequestedVal("st", "0"));
            eTime = Utility.ToInt(Utility.GetRequestedVal("et", "0"));



            size = Utility.ToInt(Utility.GetRequestedVal("size", "0"));
            quality = Utility.GetRequestedVal("quality", "");

            typeofads = Utility.ToInt(Utility.GetRequestedVal("typeofads", "0"));


            sPrice = Utility.ToInt(Utility.GetRequestedVal("sp", "0"));
            ePrice = Utility.ToInt(Utility.GetRequestedVal("ep", "0"));

            firstAppearanceOnly = Utility.ToBool(Utility.GetRequestedVal("fa", "0"));

            cols = Utility.GetRequestedVal("cols", "ProdVersionCode, ProductCode, ProdVersionName, ProductName, Price");

            cols = "MediaTypeCode, ProdVersionCode, ProductCode, OutdoorFile, NewOutdoorFile, " + cols;

            if (sDate != "1/1/2000" || eDate != "1/1/2000")
                cols = "RFDate, " + cols;

            if (!firstAppearanceOnly)
            {
                // cols += ", ShortVideo, FullVideo, ShortVideoFile, FullVideoFile, Duration, Price";
                cols += ", Price";
            }
            else
            {
                cols = cols.Replace("RFDate,", "");
                //cols += ", RFDate, ShortVideo, FullVideo, ShortVideoFile, FullVideoFile, Duration, Price";
                cols += ", RFDate, Price";
            }

            cols += ", NewOutDoorFile";

            objDSExport.SelectParameters["userID"].DefaultValue = AppUser.CurrUserID.ToString();
            objDSExport.SelectParameters["sDate"].DefaultValue = sDate.ToString();
            objDSExport.SelectParameters["eDate"].DefaultValue = eDate.ToString();
            objDSExport.SelectParameters["mediaTypeCode"].DefaultValue = mediaTypeCode.ToString();
            objDSExport.SelectParameters["mediaCodes"].DefaultValue = mediaCodes;
            objDSExport.SelectParameters["sectorCode"].DefaultValue = sectorCode.ToString();
            objDSExport.SelectParameters["subSectorCode"].DefaultValue = subSectorCode.ToString();
            objDSExport.SelectParameters["categoryCode"].DefaultValue = categoryCode.ToString();
            objDSExport.SelectParameters["advertiserCode"].DefaultValue = advertiserCode.ToString();
            objDSExport.SelectParameters["brandCode"].DefaultValue = brandCode.ToString();
            objDSExport.SelectParameters["productCode"].DefaultValue = productCode.ToString();
            objDSExport.SelectParameters["prodVersionCode"].DefaultValue = versionCode.ToString();
            objDSExport.SelectParameters["agencyCode"].DefaultValue = agencyCode.ToString();
            objDSExport.SelectParameters["sPrice"].DefaultValue = sPrice.ToString();
            objDSExport.SelectParameters["ePrice"].DefaultValue = ePrice.ToString();
            objDSExport.SelectParameters["size"].DefaultValue = size.ToString();
            objDSExport.SelectParameters["quality"].DefaultValue = quality.ToString();
            objDSExport.SelectParameters["typeOfAds"].DefaultValue = typeofads.ToString();
            objDSExport.SelectParameters["fa"].DefaultValue = firstAppearanceOnly.ToString();
        }

        protected void btnExportSummary_Clicked(object sender, EventArgs e)
        {
            String GetCols = String.Empty;

            DataView DvsummaryGet = (DataView)ViewState["dvSummaryOutDoor"];

            try
            {
                DvsummaryGet.Table.Columns.Remove("MediaType");
                DvsummaryGet.Table.Columns.Remove("InvestmentGrid");
            }
            catch { }
            DataTable dtSummary = DvsummaryGet.ToTable();
            dtSummary.Columns["MediaTypeName"].ColumnName = "Media";
            dtSummary.Columns["CountRecord"].ColumnName = "Total Records";
            dtSummary.Columns["Investment"].ColumnName = "Total Investment";

            InngridExport();
            

            GetCols = cols;// from InngridExport()

            DataView dv = new DataView();
            dv = (DataView)objDSExport.Select();

            dv = RemoveDataViewColumns(dv, GetCols);
            dv.Table.Columns.Remove("TotalRecords");

            DataTable dt = dv.ToTable();
            DumpExcelSummary(dt, dtSummary);
        }


        private void DumpExcelSummary(DataTable tbl, DataTable tblSummary)
        {

            Int32 tblColumnCount = tbl.Columns.Count;
            String strAutofilter = String.Empty;
            strAutofilter = ReturnCharacterByPassingInterger(tblColumnCount);

            if (tblColumnCount > 0)
            {

                using (ExcelPackage pck = new ExcelPackage())
                {

                    try
                    {

                        //Create the worksheet
                        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("MediaMonitor-OutdoorReport");

                        DataTable UserSelection = CreateDatatableofUserSelection();
                        ws.Cells["A1"].LoadFromDataTable(UserSelection, true);

                        ws.Cells["A10"].LoadFromDataTable(tblSummary, true);
                        ws.Cells["A13"].LoadFromDataTable(tbl, true);
                        ws.DefaultColWidth = 20;
                        ws.Cells["A13:" + strAutofilter + "13"].AutoFilter = true;
                        using (ExcelRange rng = ws.Cells["A13:" + strAutofilter + "13"])
                        {
                            rng.Style.Font.Bold = true;

                        }
                        using (ExcelRange rngSummary = ws.Cells["A10:C10"])
                        {
                            rngSummary.Style.Font.Bold = true;

                        }
                        using (ExcelRange rngUserSelection = ws.Cells["A1:B1"])
                        {
                            rngUserSelection.Style.Font.Bold = true;

                        }
                    }
                    catch { }
                    Response.Clear();
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;  filename=MediaMonitor-OutdoorReport.xlsx");
                    Response.BinaryWrite(pck.GetAsByteArray());
                    Response.End();
                }
            }
        }

        private DataView RemoveDataViewColumns(DataView dv, String cols)
        {

            if (!cols.Contains("RFDate"))
            {
                dv.Table.Columns.Remove("Date");
            }
            if (!cols.Contains("MediaTypeName"))
            {
                dv.Table.Columns.Remove("Media");
            }
            if (!cols.Contains("MediaName"))
            {
                dv.Table.Columns.Remove("Channel");
            }

            if (!cols.Contains("SectorName"))
            {
                dv.Table.Columns.Remove("Sector");
            }
            if (!cols.Contains("SubSectorName"))
            {
                dv.Table.Columns.Remove("SubSector");
            }
            if (!cols.Contains("CategoryName"))
            {
                dv.Table.Columns.Remove("Category");
            }

            if (!cols.Contains("AgencyName"))
            {
                dv.Table.Columns.Remove("Agency");
            }
            if (!cols.Contains("AdvertiserName"))
            {
                dv.Table.Columns.Remove("Advertiser");
            }
            if (!cols.Contains("BrandName"))
            {
                dv.Table.Columns.Remove("Brand");
            }
            if (!cols.Contains("ProductName"))
            {
                dv.Table.Columns.Remove("Product");
            }
            if (!cols.Contains("ProdVersionName"))
            {
                dv.Table.Columns.Remove("Version");
            }

            if (!cols.Contains("TimeStartFormatted"))
            {
                dv.Table.Columns.Remove("StartTime");
            }
            if (!cols.Contains("TimeEndFormatted"))
            {
                dv.Table.Columns.Remove("EndTime");
            }
           
            if (!cols.Contains("RealCost"))
            {
                dv.Table.Columns.Remove("Investment");
            }

         
            if (!cols.Contains("Size"))
            {
                dv.Table.Columns.Remove("Size");
            }
            if (!cols.Contains("Quality"))
            {
                dv.Table.Columns.Remove("Quality");
            }
            if (!cols.Contains("TypeOfAds"))
            {
                dv.Table.Columns.Remove("TypeOfAds");
            }
            if (!cols.Contains("VisibilityCode"))
            {
                dv.Table.Columns.Remove("Visibility");
            }
            if (!cols.Contains("CarrierCode"))
            {
                dv.Table.Columns.Remove("Carrier");
            }
            if (!cols.Contains("BetweenAvenue"))
            {
                dv.Table.Columns.Remove("BetweenAvenue");
            }


            return dv;
        }

        protected void radGridAll_ItemCommand(object source, GridCommandEventArgs e)
        {
            if (e.CommandName == "GetDetail")
            {
                Int32 MediaType = Utility.ToInt(e.Item.OwnerTableView.DataKeyValues[e.Item.ItemIndex]["MediaType"].ToString());
                Int32 CountRecord = Utility.ToInt(e.Item.OwnerTableView.DataKeyValues[e.Item.ItemIndex]["CountRecord"].ToString());
                if (CountRecord == 0)
                { }
                else
                {



                    Inngrid();
                    objDS.SelectParameters["MediaTypeCode"].DefaultValue = MediaType.ToString();
                    objDSExport.SelectParameters["MediaTypeCode"].DefaultValue = MediaType.ToString();
                    Session["mtc"] = Convert.ToInt32(MediaType.ToString());

                    radGrid.DataSourceID = "objDS";


                    radGridAll.Visible = false;
                    SummaryDIVTOP.Visible = false;

                    InnerDIV.Style.Add("display", "block");

                    InnerDIVTOP.Visible = true;



                }

            }//end GetDetail
        }
        protected void ObjDS_SelectingExport(object sender, ObjectDataSourceSelectingEventArgs e)
        {
            if (!e.ExecutingSelectCount)
            {
                e.Arguments.MaximumRows = 1;
                e.InputParameters.Add("e", e);
            }

        }

        private String ReturnCharacterByPassingInterger(Int32 tblColumnCount)
        {
            String strAutofilter = String.Empty;

            switch (tblColumnCount)
            {
                case 1:
                    {
                        strAutofilter = "A"; break;
                    }
                case 2:
                    {
                        strAutofilter = "B"; break;
                    }
                case 3:
                    {
                        strAutofilter = "C"; break;
                    }
                case 4:
                    {
                        strAutofilter = "D"; break;
                    }
                case 5:
                    {
                        strAutofilter = "E"; break;
                    }
                case 6:
                    {
                        strAutofilter = "F"; break;
                    }
                case 7:
                    {
                        strAutofilter = "G"; break;
                    }
                case 8:
                    {
                        strAutofilter = "H"; break;
                    }
                case 9:
                    {
                        strAutofilter = "I"; break;
                    }
                case 10:
                    {
                        strAutofilter = "J"; break;
                    }
                case 11:
                    {
                        strAutofilter = "K"; break;
                    }
                case 12:
                    {
                        strAutofilter = "L"; break;
                    }
                case 13:
                    {
                        strAutofilter = "M"; break;
                    }
                case 14:
                    {
                        strAutofilter = "N"; break;
                    }
                case 15:
                    {
                        strAutofilter = "O"; break;
                    }
                case 16:
                    {
                        strAutofilter = "P"; break;
                    }
                case 17:
                    {
                        strAutofilter = "Q"; break;
                    }
                case 18:
                    {
                        strAutofilter = "R"; break;
                    }
                case 19:
                    {
                        strAutofilter = "S"; break;
                    }
                case 20:
                    {
                        strAutofilter = "T"; break;
                    }
                case 21:
                    {
                        strAutofilter = "U"; break;
                    }
                case 22:
                    {
                        strAutofilter = "V"; break;
                    }
                case 23:
                    {
                        strAutofilter = "W"; break;
                    }
                case 24:
                    {
                        strAutofilter = "X"; break;
                    }
                case 25:
                    {
                        strAutofilter = "Y"; break;
                    }
                case 26:
                    {
                        strAutofilter = "Z"; break;
                    }

            }

            return strAutofilter;
        }

        private DataTable CreateDatatableofUserSelection()
        {
            String SectorName = String.Empty;
            String SubSectorName = String.Empty;
            String CategoryName = String.Empty;
            String AdvertiserName = String.Empty;
            String AgencyName = String.Empty;
            String BrandName = String.Empty;
            String VersionName = String.Empty;


            SectorName = Utility.GetRequestedVal("zs", "");
            SubSectorName = Utility.GetRequestedVal("zss", "");
            CategoryName = Utility.GetRequestedVal("zc", "");
            AdvertiserName = Utility.GetRequestedVal("zad", "");
            BrandName = Utility.GetRequestedVal("zp", ""); //product
            AgencyName = Utility.GetRequestedVal("zag", "");
            VersionName = Utility.GetRequestedVal("zve", ""); ;


            DataTable tableUserSelection = new DataTable();
            tableUserSelection.Columns.Add("Products", typeof(string));
            tableUserSelection.Columns.Add("User Selection", typeof(string));

            tableUserSelection.Rows.Add("Sector", SectorName);
            tableUserSelection.Rows.Add("Sub Sector", SubSectorName);
            tableUserSelection.Rows.Add("Category", CategoryName);
            tableUserSelection.Rows.Add("Advertiser", AdvertiserName);
            tableUserSelection.Rows.Add("Agency", AgencyName);
            tableUserSelection.Rows.Add("Product", BrandName);
            tableUserSelection.Rows.Add("Version", VersionName);

            return tableUserSelection;
        }


        protected void btnDownloadAllAdv_Clicked(object sender, EventArgs e)
        {
            CommonDownloadAllAdvertiser();
        }
        protected void btnDownloadAllSummaryAdvertiser_Clicked(object sender, EventArgs e)
        {
            CommonDownloadAllAdvertiser();
        }

        protected void btnDownloadAllProduct_Clicked(object sender, EventArgs e)
        {
            CommonDownloadAllProduct();
        }


        protected void btnDownloadAllSummaryProduct_Clicked(object sender, EventArgs e)
        {
            CommonDownloadAllProduct();
        }

        private void CommonDownloadAllProduct()
        {
            Int32 MediaType = 0;
            Int32 ProductID = 0;
            String ProductName = String.Empty;
            MediaType = Utility.ToInt(Utility.GetRequestedVal("mtc", "0"));
            ProductID = Utility.ToInt(Utility.GetRequestedVal("brnd", "0"));
            DataTable dt = RfController.GetVideosAudiosProduct(MediaType, ProductID);

            if (dt.Rows.Count > 0)
            {
                #region[Zip Format]
                Response.Clear();
                // no buffering - allows large zip files to download as they are zipped
                Response.BufferOutput = false;
                String filePath = String.Empty;
                String filePath1 = String.Empty;
                String downloadFileName = dt.Rows[0]["BrandName"].ToString().Replace("/", "") + ".zip";
                downloadFileName = downloadFileName.Replace(" ", "_");

                Response.ContentType = "application/zip";
                Response.AddHeader("content-disposition", "filename=" + downloadFileName);

                using (ZipFile zip = new ZipFile())
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        String filePathCheck = String.Empty;
                        String filePathCheck1 = String.Empty;

                        String CatName = String.Empty;
                        filePath = row["NewOutDoorFile"].ToString();
                        filePath1 = row["StreetGraph"].ToString();

                        CatName = row["BrandName"].ToString().Replace("/", "");
                        CatName = CatName.Replace(" ", "_"); ;

                        filePathCheck = Server.MapPath("../outdoor/" + filePath);
                        filePathCheck1 = Server.MapPath("../outdoor-street/" + filePath1);

                        if (filePath != "")
                        {
                            if (File.Exists(filePathCheck))
                            {
                                try
                                {
                                    zip.AddFile(filePathCheck, CatName);
                                }
                                catch { }
                            }
                        }

                        if (filePath1 != "")
                        {
                            if (File.Exists(filePathCheck1))
                            {
                                try
                                {
                                    zip.AddFile(filePathCheck1, CatName);
                                }
                                catch { }
                            }
                        }

                    }
                    zip.Save(Response.OutputStream);
                }
                Response.Close();
                Response.End();


                #endregion
            }
            else
            {
                AjaxFuncs.MsgBox(this.Page, "No record exists!");
            }
        }

        private void CommonDownloadAllAdvertiser()
        {
            Int32 MediaType = 0;
            Int32 AdvertiserID = 0;
            String AdvertiserName = String.Empty;
            MediaType = Utility.ToInt(Utility.GetRequestedVal("mtc", "0"));
            AdvertiserID = Utility.ToInt(Utility.GetRequestedVal("adv", "0"));
            DataTable dt = RfController.GetVideosAudiosAdvertiser(MediaType, AdvertiserID);

            if (dt.Rows.Count > 0)
            {
                #region[Zip Format]
                Response.Clear();
                // no buffering - allows large zip files to download as they are zipped
                Response.BufferOutput = false;
                String filePath = String.Empty;
                String filePath1 = String.Empty;
                String downloadFileName = dt.Rows[0]["AdvertiserName"].ToString().Replace("/", "") + ".zip";
                downloadFileName = downloadFileName.Replace(" ", "_");

                Response.ContentType = "application/zip";
                Response.AddHeader("content-disposition", "filename=" + downloadFileName);

                using (ZipFile zip = new ZipFile())
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        String filePathCheck = String.Empty;
                        String filePathCheck1 = String.Empty;

                        String CatName = String.Empty;
                        filePath = row["NewOutDoorFile"].ToString();
                        filePath1 = row["StreetGraph"].ToString();

                        CatName = row["AdvertiserName"].ToString().Replace("/", "");
                        CatName = CatName.Replace(" ", "_"); ;

                        filePathCheck = Server.MapPath("../outdoor/" + filePath);
                        filePathCheck1 = Server.MapPath("../outdoor-street/" + filePath1);

                        if (filePath != "")
                        {
                            if (File.Exists(filePathCheck))
                            {
                                try
                                {
                                    zip.AddFile(filePathCheck, CatName);
                                }
                                catch { }
                            }
                        }

                        if (filePath1 != "")
                        {
                            if (File.Exists(filePathCheck1))
                            {
                                try
                                {
                                    zip.AddFile(filePathCheck1, CatName);
                                }
                                catch { }
                            }
                        }

                    }
                    zip.Save(Response.OutputStream);
                }
                Response.Close();
                Response.End();


                #endregion
            }
            else
            {
                AjaxFuncs.MsgBox(this.Page, "No record exists!");
            }
        }

    }
}